import openpyxl

class ExcelOp(object):
    def __init__(self, filename):
        self.filename = filename
        self.wb = openpyxl.load_workbook(self.filename)
        sheets = self.wb.sheetnames
        self.sheet = sheets[0]
        self.ws = self.wb[self.sheet]

    def get_row_value(self):
        columns = self.ws.max_column
        rows = self.ws.max_row
        result = []
        for r in range(1,rows+1):
            data = []
            for c in range(1,columns+1):
                info = self.ws.cell(row=r,column=c).value
                data.append(info)
            result.append(data)
        return result
    
    def create_sheet(self, result):
        #self.ws1 = self.wb.create_sheet('整理',0)
        count = 0
        for data in result:
            if ';' in data[1]:
                for _ in range(data[1].count(';')):
                    self.ws.insert_rows(result.index(data)+1+count)
                count = data[1].count(';') + count
        self.wb.save(self.filename)

    def split_data(self):
        result = self.get_row_value()
        count = 0
        for data in result:
            info = []
            print(data)
            if ';' in data[1]:
                lines = result.index(data)+1
                for i in range(data[1].count(';')+1):
                    info.append(data[1].split(';')[i])
                    info.append(data[2].split(';')[i])
                for _ in range(data[1].count(';')):
                    self.ws.insert_rows(result.index(data)+1+count)
                for i in range(data[1].count(';')+1):
                    self.ws.cell(row=lines+i+count,column=1,value=data[0])
                    self.ws.cell(row=lines+i+count,column=2,value=info[i+i])
                    self.ws.cell(row=lines+i+count,column=3,value=info[i+i+1]) 
                    self.ws.cell(row=lines+i+count,column=4,value=data[3])  
                    self.ws.cell(row=lines+i+count,column=5,value=data[4])
                count = data[1].count(';') + count
                    
        self.wb.save(self.filename)

if __name__ == "__main__":
    filename = r"C:\Users\tujuan\Desktop\割接不可用度\8月割接不可用度\8.13-8.19\demo.xlsx"
    excel_op = ExcelOp(filename=filename)
    #data = excel_op.get_row_value()
    #excel_op.create_sheet(data)
    excel_op.split_data()
